package com.kedacom.freedomLabel.utils;

import android.text.TextUtils;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.kedacom.freedomLabel.model.bean.Category;
import com.kedacom.freedomLabel.model.bean.Item;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelImportUtils {

    /**
     * 读取categories
     *
     * @param inputStream
     * @return
     */
    public static List<Category> readCategories(InputStream inputStream) throws IOException, BiffException {
        List<Category> categories = new ArrayList<>();
        Workbook book = Workbook.getWorkbook(inputStream);
        Sheet sheet = book.getSheet(0);
        // 跳过第一行，第一行为标题
        for (int i = 1; i < sheet.getRows(); i++) {
            Cell cell = null;
            Category c = new Category();
            c.columnNames = new String[6];

            for (int j = 0; j < 9; j++) {
                cell = sheet.getCell(j, i);
                switch (j) {
                    case 0:
                        c.name = cell.getContents();
                        break;
//                    case 1:
//                        c.low = cell.getContents();
//                        break;
//                    case 2:
//                        c.upper = cell.getContents();
//                        break;
                    case 3:
                        c.columnNames[0] = TextUtils.isEmpty(cell.getContents()) ? "ID1" : cell.getContents();
                        break;
                    case 4:
                        c.columnNames[1] = TextUtils.isEmpty(cell.getContents()) ? "ID2" : cell.getContents();
                        break;
                    case 5:
                        c.columnNames[2] = TextUtils.isEmpty(cell.getContents()) ? "ID3" : cell.getContents();
                        break;
                    case 6:
                        c.columnNames[3] = TextUtils.isEmpty(cell.getContents()) ? "ID4" : cell.getContents();
                        break;
                    case 7:
                        c.columnNames[4] = TextUtils.isEmpty(cell.getContents()) ? "ID5" : cell.getContents();
                        break;
                    case 8:
                        c.columnNames[5] = TextUtils.isEmpty(cell.getContents()) ? "ID6" : cell.getContents();
                        break;
                }
            }
            categories.add(c);
        }
        return categories;
    }

    /**
     * 读取categories
     *
     * @param file
     * @return
     */
    public static List<Category> readCategories(File file) throws IOException, BiffException {
        return readCategories(new FileInputStream(file));
    }


    public static <T> List<T> readSheet(InputStream inputStream, String sheetName, String[] titleArr, String[] keyArr,Boolean[] isArr, Type type) throws IOException, BiffException {

        Workbook book = Workbook.getWorkbook(inputStream);
        Sheet sheet = book.getSheet(sheetName);
        Gson gson = new Gson();
        if (sheet == null)
            return null;

        Cell[] firstRow = sheet.getRow(0);
        List<Map<String, Object>> list = new ArrayList<>();

        for (int row = 1; row < sheet.getRows(); row++) {
            Map<String, Object> cell = new HashMap<>();
            boolean notEmpty = false;

            for (int column = 0; column < firstRow.length; column++) {
                for (int index = 0; index < titleArr.length; index++) {
                    if (sheet.getCell(column, 0).getContents().equals(titleArr[index])) {
                        if(!TextUtils.isEmpty(sheet.getCell(column, row).getContents())){
                            notEmpty = true;
                        }
                        if(isArr[index]){
                            cell.put(keyArr[index], sheet.getCell(column, row).getContents().split(","));
                        }else {
                            cell.put(keyArr[index], sheet.getCell(column, row).getContents());
                        }
                    }
                }
            }
            if(notEmpty){
                list.add(cell);
            }
        }
        return gson.fromJson(gson.toJson(list), type);
    }

    /**
     * 获取Categories的所有items
     *
     * @param inputStream
     * @return
     */
    public static List<List<List<Item>>> readItemList(InputStream inputStream, List<Category> categories) throws IOException, BiffException {
        List<List<List<Item>>> res = new ArrayList<>();

        Workbook book = Workbook.getWorkbook(inputStream);
        String[] sheetNames = book.getSheetNames();

        for (int i = 0; i < categories.size(); i++) {
            boolean found = false;
            for (int j = 0; j < sheetNames.length; j++) {
                // 找到categories对应sheet
                if (categories.get(i).name.equals(sheetNames[j])) {
                    res.add(readItemFromSheet(categories.get(i).columnNames, book.getSheet(j)));
                    found = true;
                    break;
                }
            }
            if (!found) res.add(new ArrayList<>());
        }

        return res;
    }

    /**
     * 从sheet中读取item信息
     *
     * @return
     */
    private static List<List<Item>> readItemFromSheet(String[] columnNames, Sheet sheet) {
        List<List<Item>> itemsList = new ArrayList<>();
        // 获取第一行
        Cell[] firstRow = sheet.getRow(0);
        for (int i = 0; i < columnNames.length; i++) {
            List<Item> tempItemList = new ArrayList<>();

            if (TextUtils.isEmpty(columnNames[i])) {
                itemsList.add(tempItemList);
                continue;
            }

            for (int j = 0; j < firstRow.length; j++) {
                if (columnNames[i].equals(firstRow[j].getContents())) {
                    int rows = 1; // 从第一行开始度
                    while (rows < sheet.getRows() &&
                            !TextUtils.isEmpty(sheet.getCell(j, rows).getContents())) {
                        Item item = new Item();
                        item.category = "ID" + (i + 1);
                        item.value = sheet.getCell(j, rows).getContents();
//                        item.category = String.valueOf(i)
                        tempItemList.add(item);
                        rows++;
                    }
                    break;
                }
            }
            itemsList.add(tempItemList);
        }
        return itemsList;
    }
}
























